﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace UniversityStudentManagemnetSystem.DAO.DAL.Gateway
{
    public class StudentRegistationGateway:DAO.Gateway.Gateway
    {
        public bool SaveStudentRegistationProcess(Student student)
        {
            try
            {
              Connection.Open();

                string insertQuery =
                    string.Format(
                        "insert into t_StudentRegistation values(@Name,@Email,@Contact_No,@Address,@DepartmentId,@RegistationDate,@RegNo)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Name", SqlDbType.VarChar);
                Command.Parameters["@Name"].Value = student.Name;
                Command.Parameters.Add("@Email", SqlDbType.VarChar);
                Command.Parameters["@Email"].Value = student.Email;
                Command.Parameters.Add("@Contact_No", SqlDbType.VarChar);
                Command.Parameters["@Contact_No"].Value = student.ContactNumber;
                Command.Parameters.Add("@Address", SqlDbType.VarChar);
                Command.Parameters["@Address"].Value = student.Address;
                Command.Parameters.Add("@DepartmentId", SqlDbType.Int);
                Command.Parameters["@DepartmentId"].Value = student.Department.DeptId;
                Command.Parameters.Add("@RegistationDate", SqlDbType.VarChar);
                Command.Parameters["@RegistationDate"].Value = student.RegistrationDate;
                Command.Parameters.Add("@RegNo", SqlDbType.VarChar);
                Command.Parameters["@RegNo"].Value = student.RegNo;
                Command.ExecuteNonQuery();

            }
            catch (Exception exception)
            {

                throw new Exception("Save couldn't performed due to system error!", exception);

            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }

        public bool HasThisEmail(Student aStudent)
        {
            bool hasRows = false;
            try
            {

                Connection.Open();
                string selectQuery = string.Format("select Email from t_StudentRegistation where Email=@email");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@email", SqlDbType.VarChar);
                Command.Parameters["@email"].Value = aStudent.Email;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;

                }
            }
            catch (Exception exception)
            {
                
                throw new Exception("Sorry Unknown Error");
            }

           finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
         
            return hasRows;
                
            
        }


        public int PreviousStudentCount(int deptId)
        {
            
            Command.CommandText =
               
                    @"select * from t_StudentRegistation where  DepartmentId = @deptId";
            Command.Parameters.Clear();
            Command.Parameters.Add("@deptId", SqlDbType.Int).Value = deptId;
           // Command.Parameters.Add("@regYear", SqlDbType.VarChar).Value ="%"+ regDate +"%";
            queryString = GetQueryString(Command);

            DataSet dataSet = new DataSet();
            DataTable dataTable;
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, Connection.ConnectionString);
                dataAdapter.Fill(dataSet);
                dataTable = dataSet.Tables[0];
            }
            catch (Exception exception)
            {
                throw new Exception("Database Error", exception);
            }
              finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }

            return dataTable.Rows.Count;

        }

        public string GetCodefor(int deptId)
        {

            string selectQuery = string.Format("Select Code from t_department where Id=@deptId");
            Command.CommandText = selectQuery;
            Command.Parameters.Clear();
            Command.Parameters.Add("@deptId", SqlDbType.Int).Value = deptId;
            queryString = GetQueryString(Command);
            string departmentCode = null;
            try
            {
                Connection.Open();
                DataReader = Command.ExecuteReader();

                if (DataReader.HasRows)
                {
                    while (DataReader.Read())
                    {
                        departmentCode = DataReader["Code"].ToString();
                    }
                }
                else

                  {  departmentCode = null;
                 }
                    }

    catch (Exception exception)
            {

                throw new Exception("Database Error", exception);
            }
            finally
            {

                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return departmentCode;

        }


        internal void SaveStudentEducationInformation(Student studentEducation, StudentEducationRegistration education, string studentRegNo)
        {

            try
            {

                Connection.Open();
                string insertQuery = string.Format("insert into t_Education values (@Exam,@Grade,@Certificate,@Email,@Cgpa,@Location)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@Exam", SqlDbType.VarChar);
                Command.Parameters["@Exam"].Value = education.ExamTitle; 
                Command.Parameters.Add("@Grade", SqlDbType.VarChar);
                Command.Parameters["@Grade"].Value = education.GradeLetterOthers; 
                Command.Parameters.Add("@Certificate", SqlDbType.VarChar);
                Command.Parameters["@Certificate"].Value = education.CertificateName;
                Command.Parameters.Add("@Email", SqlDbType.VarChar);
                Command.Parameters["@Email"].Value = studentEducation.Email;
                Command.Parameters.Add("@Cgpa", SqlDbType.Float);
                Command.Parameters["@Cgpa"].Value = education.CgpaPercentage;
                Command.Parameters.Add("@Location", SqlDbType.VarChar);
                Command.Parameters["@Location"].Value = education.Location;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                
                throw new Exception("Database Error");
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
        }

        public DataTable GetCurrentStudentAllCertificate(Student aStudent)
        {
            string selectQuery = string.Format("select * from t_Education where Email='"+aStudent.Email+"'");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet, "EducationTable");
            DataTable myDataTable = dataSet.Tables["EducationTable"];
            return myDataTable;
        }
    }
}